# -*- coding:utf-8 -*-
# 2022/1/16 10:39
# author:xwl
import datetime
import operator
import traceback
import backend.app.app.models.db_owi_public_models as md_pub
from backend.app.app.api.api_Hu.crud.pro.tb_userpro_input import create_tree
from backend.app.app.api.effect_pridict.base_infomation import return_tree
from backend.app.app.db.session_public import session_pub


def db_fuc1(planproj_name, dvpunit_name, year, stimtype_name):
    try:
        datas = session_pub.query(
            md_pub.TbYearDvpunitOilstimPlanprojForecast.DvpUnit_OilStim_PlanProj_Name,
            md_pub.TbYearDvpunitOilstimPlanprojForecast.DvpUnit_Name,
            md_pub.TbYearDvpunitOilstimPlanprojForecast.Year,
            md_pub.TbYearDvpunitOilstimPlanprojForecast.StimType_Name,
        ).filter(
            md_pub.TbYearDvpunitOilstimPlanprojForecast.DvpUnit_OilStim_PlanProj_Name.in_(planproj_name),
            md_pub.TbYearDvpunitOilstimPlanprojForecast.DvpUnit_Name.in_(dvpunit_name),
            md_pub.TbYearDvpunitOilstimPlanprojForecast.Year >= year[0],
            md_pub.TbYearDvpunitOilstimPlanprojForecast.Year <= year[1],
            md_pub.TbYearDvpunitOilstimPlanprojForecast.StimType_Name.in_(stimtype_name),
        ).all()
        datas = [dict(zip(data.keys(), data)) for data in datas]
        result = list()
        if datas:
            for data in datas:
                info = session_pub.query(
                    md_pub.TbYearDvpunitOilstimPlanprojForecast.DvpUnit_OilStim_PlanProj_Name,
                    md_pub.TbYearDvpunitOilstimPlanprojForecast.DvpUnit_Name,
                    md_pub.TbYearDvpunitOilstimPlanprojForecast.Year,
                    md_pub.TbYearDvpunitOilstimPlanprojForecast.StimType_Name,
                    md_pub.TbYearDvpunitOilstimPlanprojForecast.Forecast_Method,
                    md_pub.TbYearDvpunitOilstimPlanprojForecast.Stim_WellValidRatio,
                    md_pub.TbYearDvpunitOilstimPlanprojForecast.Stim_WellNumber_Valid,
                    md_pub.TbYearDvpunitOilstimPlanprojForecast.Stim_ValidPeriod_Average,
                    md_pub.TbYearDvpunitOilstimPlanprojForecast.Stim_IncreOil_Average,
                    md_pub.TbYearDvpunitOilstimPlanprojForecast.Update_Date,

                    md_pub.TbYearDvpunitPlanprojOilstimCost.Stim_Cost,

                    md_pub.TbYearDvpunitPlanprojOilprice.Oil_CommodityRatio,
                    md_pub.TbYearDvpunitPlanprojOilprice.Oil_Price,

                    md_pub.TbYearDvpunitPlanprojDiscount.Money_Discount,
                    md_pub.TbYearDvpunitPlanprojDiscount.Remark,
                ).filter(
                    md_pub.TbYearDvpunitOilstimPlanprojForecast.DvpUnit_OilStim_PlanProj_Name == data[
                        'DvpUnit_OilStim_PlanProj_Name'],
                    md_pub.TbYearDvpunitOilstimPlanprojForecast.DvpUnit_Name == data['DvpUnit_Name'],
                    md_pub.TbYearDvpunitOilstimPlanprojForecast.Year == data['Year'],
                    md_pub.TbYearDvpunitOilstimPlanprojForecast.StimType_Name == data['StimType_Name'],

                    md_pub.TbYearDvpunitPlanprojOilstimCost.DvpUnit_OilStim_PlanProj_Name == data[
                        'DvpUnit_OilStim_PlanProj_Name'],
                    md_pub.TbYearDvpunitPlanprojOilstimCost.DvpUnit_Name == data['DvpUnit_Name'],
                    md_pub.TbYearDvpunitPlanprojOilstimCost.Year == data['Year'],
                    md_pub.TbYearDvpunitPlanprojOilstimCost.StimType_Name == data['StimType_Name'],

                    md_pub.TbYearDvpunitPlanprojOilprice.DvpUnit_OilStimulate_PlanProj_Name == data[
                        'DvpUnit_OilStim_PlanProj_Name'],
                    md_pub.TbYearDvpunitPlanprojOilprice.DvpUnit_Name == data['DvpUnit_Name'],
                    md_pub.TbYearDvpunitPlanprojOilprice.Year == data['Year'],

                    md_pub.TbYearDvpunitPlanprojDiscount.DvpUnit_OilStimulate_PlanProj_Name == data[
                        'DvpUnit_OilStim_PlanProj_Name'],
                    md_pub.TbYearDvpunitPlanprojDiscount.DvpUnit_Name == data['DvpUnit_Name'],
                    md_pub.TbYearDvpunitPlanprojDiscount.Year == data['Year'],
                ).first()
                if info:
                    info = dict(zip(info.keys(), info))
                    result.append(info)
        return result
    except:
        print(traceback.print_exc())
        return False


def read_oilprod_cost(planproj_name, dvpunit_name, year):
    try:
        datas = session_pub.query(
            md_pub.TbYearDvpunitPlanprojOilprodcost
        ).filter(
            md_pub.TbYearDvpunitPlanprojOilprodcost.DvpUnit_OilStim_PlanProj_Name.in_(planproj_name),
            md_pub.TbYearDvpunitPlanprojOilprodcost.DvpUnit_Name.in_(dvpunit_name),
            md_pub.TbYearDvpunitPlanprojOilprodcost.Year >= year[0],
            md_pub.TbYearDvpunitPlanprojOilprodcost.Year <= year[1]
        ).all()
        result = [data.__dict__ for data in datas]
        return result
    except:
        print(traceback.print_exc())
        return False


def read_oilsalecost_ratio(planproj_name, dvpunit_name, year):
    try:
        datas = session_pub.query(
            md_pub.TbYearDvpunitPlanprojOilsalecostRatio
        ).filter(
            md_pub.TbYearDvpunitPlanprojOilsalecostRatio.DvpUnit_OilStim_PlanProj_Name.in_(planproj_name),
            md_pub.TbYearDvpunitPlanprojOilsalecostRatio.DvpUnit_Name.in_(dvpunit_name),
            md_pub.TbYearDvpunitPlanprojOilsalecostRatio.Year >= year[0],
            md_pub.TbYearDvpunitPlanprojOilsalecostRatio.Year <= year[1]
        ).all()
        result = [data.__dict__ for data in datas]
        return result
    except:
        print(traceback.print_exc())
        return False


def eva_cal_save_data(datas):
    try:
        save_success = list()
        for data in datas:
            db_data = session_pub.query(md_pub.TbYearDvpunitOilstimPlanprojForecast).filter(
                md_pub.TbYearDvpunitOilstimPlanprojForecast.DvpUnit_OilStim_PlanProj_Name == data[
                    'DvpUnit_OilStim_PlanProj_Name'],
                md_pub.TbYearDvpunitOilstimPlanprojForecast.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitOilstimPlanprojForecast.Year == data['Year'],
                md_pub.TbYearDvpunitOilstimPlanprojForecast.StimType_Name == data['StimType_Name']
            )
            data['Update_Date'] = datetime.datetime.now().date()
            if db_data.first():
                db_data.update({
                    'Forecast_Method': data['Forecast_Method'],
                    'Stim_WellValidRatio': data['Stim_WellValidRatio'],
                    'Stim_WellNumber_Valid': data['Stim_WellNumber_Valid'],
                    'Stim_ValidPeriod_Average': data['Stim_ValidPeriod_Average'],
                    'Stim_IncreOil_Average': data['Stim_IncreOil_Average'],
                    'Update_Date': data['Update_Date'],
                    'Remark': data['Remark']
                })
            else:
                session_pub.add(md_pub.TbYearDvpunitOilstimPlanprojForecast(
                    DvpUnit_OilStim_PlanProj_Name=data['DvpUnit_OilStim_PlanProj_Name'],
                    DvpUnit_Name=data['DvpUnit_Name'],
                    Year=data['Year'],
                    StimType_Name=data['StimType_Name'],
                    Forecast_Method=data['Forecast_Method'],
                    Stim_WellValidRatio=data['Stim_WellValidRatio'],
                    Stim_WellNumber_Valid=data['Stim_WellNumber_Valid'],
                    Stim_ValidPeriod_Average=data['Stim_ValidPeriod_Average'],
                    Stim_IncreOil_Average=data['Stim_IncreOil_Average'],
                    Update_Date=data['Update_Date'],
                    Remark=data['Remark']
                ))
            session_pub.commit()

            db_data = session_pub.query(md_pub.TbYearDvpunitPlanprojOilstimCost).filter(
                md_pub.TbYearDvpunitPlanprojOilstimCost.DvpUnit_OilStim_PlanProj_Name == data[
                    'DvpUnit_OilStim_PlanProj_Name'],
                md_pub.TbYearDvpunitPlanprojOilstimCost.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitPlanprojOilstimCost.Year == data['Year'],
                md_pub.TbYearDvpunitPlanprojOilstimCost.StimType_Name == data['StimType_Name']
            )
            if db_data.first():
                db_data.update({
                    'Stim_Cost': data['Stim_Cost'],
                    'Remark': data['Remark']
                })
            else:
                session_pub.add(md_pub.TbYearDvpunitPlanprojOilstimCost(
                    DvpUnit_OilStim_PlanProj_Name=data['DvpUnit_OilStim_PlanProj_Name'],
                    DvpUnit_Name=data['DvpUnit_Name'],
                    Year=data['Year'],
                    StimType_Name=data['StimType_Name'],
                    Stim_Cost=data['Stim_Cost'],
                    Remark=data['Remark']
                ))
            session_pub.commit()

            db_data = session_pub.query(md_pub.TbYearDvpunitPlanprojOilprice).filter(
                md_pub.TbYearDvpunitPlanprojOilprice.DvpUnit_OilStimulate_PlanProj_Name == data[
                    'DvpUnit_OilStim_PlanProj_Name'],
                md_pub.TbYearDvpunitPlanprojOilprice.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitPlanprojOilprice.Year == data['Year']
            )
            if db_data.first():
                db_data.update({
                    'Oil_CommodityRatio': data['Oil_CommodityRatio'],
                    'Oil_Price': data['Oil_Price'],
                    'Remark': data['Remark']
                })
            else:
                session_pub.add(md_pub.TbYearDvpunitPlanprojOilprice(
                    DvpUnit_OilStimulate_PlanProj_Name=data['DvpUnit_OilStim_PlanProj_Name'],
                    DvpUnit_Name=data['DvpUnit_Name'],
                    Year=data['Year'],
                    Oil_CommodityRatio=data['Oil_CommodityRatio'],
                    Oil_Price=data['Oil_Price'],
                    Remark=data['Remark']
                ))
            session_pub.commit()

            db_data = session_pub.query(md_pub.TbYearDvpunitPlanprojDiscount).filter(
                md_pub.TbYearDvpunitPlanprojDiscount.DvpUnit_OilStimulate_PlanProj_Name == data[
                    'DvpUnit_OilStim_PlanProj_Name'],
                md_pub.TbYearDvpunitPlanprojDiscount.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitPlanprojDiscount.Year == data['Year']
            )
            if db_data.first():
                db_data.update({
                    'Money_Discount': data['Money_Discount'],
                    'Remark': data['Remark']
                })
            else:
                session_pub.add(md_pub.TbYearDvpunitPlanprojDiscount(
                    DvpUnit_OilStimulate_PlanProj_Name=data['DvpUnit_OilStim_PlanProj_Name'],
                    DvpUnit_Name=data['DvpUnit_Name'],
                    Year=data['Year'],
                    Money_Discount=data['Money_Discount'],
                    Remark=data['Remark']
                ))
            session_pub.commit()

            save_success.append({
                'DvpUnit_OilStim_PlanProj_Name': data['DvpUnit_OilStim_PlanProj_Name'],
                'DvpUnit_Name': data['DvpUnit_Name'],
                'Year': data['Year'],
                'StimType_Name': data['StimType_Name'],
                'result': True
            })
        return {
            "save_success": save_success
        }
    except:
        print(traceback.print_exc())
        session_pub.rollback()
        return False


def eva_cal_output_save(datas):
    try:
        save_success = list()
        for data in datas:
            db_data = session_pub.query(md_pub.TbYearDvpunitOilstimPlanprojEvaluateResult).filter(
                md_pub.TbYearDvpunitOilstimPlanprojEvaluateResult.DvpUnit_OilStim_PlanProj_Name == data[
                    'DvpUnit_OilStim_PlanProj_Name'],
                md_pub.TbYearDvpunitOilstimPlanprojEvaluateResult.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitOilstimPlanprojEvaluateResult.Year == data['Year'],
                md_pub.TbYearDvpunitOilstimPlanprojEvaluateResult.StimType_Name == data['StimType_Name']
            ).first()
            if db_data:
                db_data.Stim_WellValidRatio = data['Stim_WellValidRatio']
                db_data.Stim_WellNumber_Valid = data['Stim_WellNumber_Valid']
                db_data.Stim_ValidPeriod_Average = data['Stim_ValidPeriod_Average']
                db_data.Stim_IncreOil_Average = data['Stim_IncreOil_Average']
                db_data.StimOil_CommodityRatio = data['StimOil_CommodityRatio']
                db_data.Oil_Price = data['Oil_Price']
                db_data.OilSale_TotalTaxRatio = data['OilSale_TotalTaxRatio']
                db_data.Money_Discount = data['Money_Discount']
                db_data.Oil_OprCost = data['Oil_OprCost']
                db_data.StimCost_Average = data['StimCost_Average']
                db_data.StimOil_WellOprCost_Average = data['StimOil_WellOprCost_Average']
                db_data.StimOil_Total_Tax_Average = data['StimOil_Total_Tax_Average']
                db_data.StimOil_Gross_SalesIncome_Average = data['StimOil_Gross_SalesIncome_Average']
                db_data.StimOil_Static_Gains_Average = data['StimOil_Static_Gains_Average']
                db_data.StimOil_NPV_Average = data['StimOil_NPV_Average']
                db_data.StimOil_ROI = data['StimOil_ROI']
                db_data.StimOil_NPVR = data['StimOil_NPVR']
                db_data.StimOil_FIRR = data['StimOil_FIRR']
                db_data.Stim_IncreOil_Critical_Average = data['Stim_IncreOil_Critical_Average']
                db_data.StimOil_Price_Critical = data['StimOil_Price_Critical']
                db_data.Remark = data['Remark']
            else:
                session_pub.add(md_pub.TbYearDvpunitOilstimPlanprojEvaluateResult(
                    DvpUnit_OilStim_PlanProj_Name=data['DvpUnit_OilStim_PlanProj_Name'],
                    DvpUnit_Name=data['DvpUnit_Name'],
                    Year=data['Year'],
                    StimType_Name=data['StimType_Name'],
                    Stim_WellValidRatio=data['Stim_WellValidRatio'],
                    Stim_WellNumber_Valid=data['Stim_WellNumber_Valid'],
                    Stim_ValidPeriod_Average=data['Stim_ValidPeriod_Average'],
                    Stim_IncreOil_Average=data['Stim_IncreOil_Average'],
                    StimOil_CommodityRatio=data['StimOil_CommodityRatio'],
                    Oil_Price=data['Oil_Price'],
                    OilSale_TotalTaxRatio=data['OilSale_TotalTaxRatio'],
                    Money_Discount=data['Money_Discount'],
                    Oil_OprCost=data['Oil_OprCost'],
                    StimCost_Average=data['StimCost_Average'],
                    StimOil_WellOprCost_Average=data['StimOil_WellOprCost_Average'],
                    StimOil_Total_Tax_Average=data['StimOil_Total_Tax_Average'],
                    StimOil_Gross_SalesIncome_Average=data['StimOil_Gross_SalesIncome_Average'],
                    StimOil_Static_Gains_Average=data['StimOil_Static_Gains_Average'],
                    StimOil_NPV_Average=data['StimOil_NPV_Average'],
                    StimOil_ROI=data['StimOil_ROI'],
                    StimOil_NPVR=data['StimOil_NPVR'],
                    StimOil_FIRR=data['StimOil_FIRR'],
                    Stim_IncreOil_Critical_Average=data['Stim_IncreOil_Critical_Average'],
                    StimOil_Price_Critical=data['StimOil_Price_Critical'],
                    Remark=data['Remark']
                ))
            session_pub.commit()
            save_success.append({
                'DvpUnit_OilStim_PlanProj_Name': data['DvpUnit_OilStim_PlanProj_Name'],
                'DvpUnit_Name': data['DvpUnit_Name'],
                'Year': data['Year'],
                'StimType_Name': data['StimType_Name'],
                'result': True
            })
        return save_success
    except:
        print(traceback.print_exc())
        session_pub.rollback()
        return False


def get_all_proj_name():
    try:
        result = set()
        db_data = session_pub.query(md_pub.TbOilwellstimPlanprojForecast.DvpUnit_OilStim_PlanProj_Name).all()
        if db_data:
            for data in db_data:
                result.add(data[0])
        return list(result)
    except:
        print(traceback.print_exc())
        return False


def get_dvpname_by_projname(allprojname):
    try:
        allwellname = set()
        alldvpname = set()
        db_data = session_pub.query(md_pub.TbOilwellstimPlanprojForecast.Well_Name).filter(
            md_pub.TbOilwellstimPlanprojForecast.DvpUnit_OilStim_PlanProj_Name.in_(allprojname)
        ).all()
        if db_data:
            for data in db_data:
                allwellname.add(data[0])
        allwellname = list(allwellname)
        db_data = session_pub.query(md_pub.TbWellbasic.Belong_DvpUnit_Name).filter(
            md_pub.TbWellbasic.Well_Name.in_(allwellname)
        ).all()
        if db_data:
            for data in db_data:
                alldvpname.add(data[0])
        alldvpname = list(alldvpname)
        return alldvpname
    except:
        print(traceback.print_exc())
        return False


# 通过开发单元找到已预测的井号并将开发单元和已预测的井号关联返回
def get_wellname_by_dvpname(alldvpname):
    try:
        result = list()
        count = 0
        for dvpname in alldvpname:
            all_well_in_wellbasic = session_pub.query(md_pub.TbWellbasic.Well_Name).filter(
                md_pub.TbWellbasic.Belong_DvpUnit_Name == dvpname
            ).all()
            all_well = list()
            if all_well_in_wellbasic:
                for well in all_well_in_wellbasic:
                    all_well.append(well[0])
            all_well_in_forecast = session_pub.query(md_pub.TbOilwellstimPlanprojForecast.Well_Name).filter(
                md_pub.TbOilwellstimPlanprojForecast.Well_Name.in_(all_well)
            ).all()
            all_well.clear()
            if all_well_in_forecast:
                for well in all_well_in_forecast:
                    all_well.append(well[0])
            if all_well:
                result.append({
                    'id': count,
                    'title': dvpname,
                    'children': childrenfun(all_well, count + 1)
                })
                count += len(all_well) + 1
            else:
                result.append({
                    'id': count,
                    'title': dvpname,
                })
                count += 1
        return result
    except:
        print(traceback.print_exc())
        return False


# 将井号列表转字典形式
def childrenfun(allchildren, start):
    result = list()
    for children in allchildren:
        result.append({
            'id': start,
            'title': children
        })
        start += 1
    return result


# 通过开发单元和井号的关联数据获取井号
def get_forecast_well(well_by_rank):
    all_well = list()
    for data in well_by_rank:
        if 'children' in data.keys():
            for children in data['children']:
                all_well.append(children['title'])
    return all_well


# 根据井号返回年度区间 参数是开发单元和井号的关联数据
def get_year_by_well(well_by_rank):
    try:
        all_well = get_forecast_well(well_by_rank)
        all_year = set()
        year_section = list()
        db_data = session_pub.query(md_pub.TbOilwellstimPlanprojForecast.Year).filter(
            md_pub.TbOilwellstimPlanprojForecast.Well_Name.in_(all_well)
        ).all()
        if db_data:
            for data in db_data:
                all_year.add(data[0])
            all_year = sorted(list(all_year))
            year_section.append(all_year[0])
            year_section.append(all_year[len(all_year) - 1])
        return year_section
    except:
        print(traceback.print_exc())
        return False


# 根据井号返回措施类别 参数是开发单元和井号的关联数据
def get_stimtype_by_well(well_by_rank):
    try:
        all_stimtype = set()
        all_well = get_forecast_well(well_by_rank)
        db_data = session_pub.query(md_pub.TbOilwellstimPlanprojForecast.StimType_Name).filter(
            md_pub.TbOilwellstimPlanprojForecast.Well_Name.in_(all_well)
        ).all()
        if db_data:
            for data in db_data:
                all_stimtype.add(data[0])
        all_stimtype = list(all_stimtype)
        db_data = session_pub.query(
            md_pub.TbOilwellstimType.StimType_Name,
            md_pub.TbOilwellstimType.Belong_StimType_Name,
            md_pub.TbOilwellstimType.StimType_Rank,
            md_pub.TbOilwellstimType.StimType_ID
        ).filter(
            md_pub.TbOilwellstimType.StimType_Name.in_(all_stimtype)
        ).all()
        all_stimtype_in_db = [dict(zip(type.keys(), type)) for type in db_data]
        stim_type_tree = list()
        stimtype_list = sorted(all_stimtype_in_db, key=lambda x: x["StimType_Rank"])
        while len(stimtype_list):
            item = stimtype_list[0]
            temp_dict = dict()
            temp_dict.update({
                'id': item['StimType_ID'],
                'title': item['StimType_Name'],
            })
            child = stimtype_tree(stimtype_list, item['StimType_Name'])
            if child:
                temp_dict.update({
                    'children': child
                })
            stim_type_tree.append(temp_dict)
            del_name = set()
            new_list = list()
            del_name.add(item['StimType_Name'])
            for x in stimtype_list:
                if x['Belong_StimType_Name'] in del_name or x['StimType_Name'] in del_name:
                    del_name.add(x['StimType_Name'])
                else:
                    new_list.append(x)
            stimtype_list = new_list
        return stim_type_tree
    except:
        print(traceback.print_exc())
        return False


def readdb_and_query_fun(dvpUnit_oilstim_planproj_name, well_name, stimtype_name, year):
    try:
        db_data = session_pub.query(
            md_pub.TbOilwellstimPlanprojForecast.DvpUnit_OilStim_PlanProj_Name,
            md_pub.TbOilwellstimPlanprojForecast.Well_Name,
            md_pub.TbOilwellstimPlanprojForecast.StimType_Name,
            md_pub.TbOilwellstimPlanprojForecast.Year
        ).filter(
            md_pub.TbOilwellstimPlanprojForecast.DvpUnit_OilStim_PlanProj_Name.in_(dvpUnit_oilstim_planproj_name),
            md_pub.TbOilwellstimPlanprojForecast.Well_Name.in_(well_name),
            md_pub.TbOilwellstimPlanprojForecast.StimType_Name.in_(stimtype_name),
            md_pub.TbOilwellstimPlanprojForecast.Year >= year[0],
            md_pub.TbOilwellstimPlanprojForecast.Year <= year[1]
        ).all()
        datas = [dict(zip(data.keys(), data)) for data in db_data]
        result = list()
        if db_data:
            for data in datas:
                dvpunit_name = session_pub.query(
                    md_pub.TbWellbasic.Belong_DvpUnit_Name
                ).filter(
                    md_pub.TbWellbasic.Well_Name == data['Well_Name']
                ).first()
                if dvpunit_name:
                    dvpunit_name = dvpunit_name[0]
                data['DvpUnit_Name'] = dvpunit_name
                db_data = session_pub.query(
                    md_pub.TbOilwellstimPlanprojForecast.DvpUnit_OilStim_PlanProj_Name,
                    md_pub.TbOilwellstimPlanprojForecast.Well_Name,
                    md_pub.TbOilwellstimPlanprojForecast.StimType_Name,
                    md_pub.TbOilwellstimPlanprojForecast.Year,
                    md_pub.TbOilwellstimPlanprojForecast.Forecast_Method,
                    md_pub.TbOilwellstimPlanprojForecast.Stim_ValidPeriod,
                    md_pub.TbOilwellstimPlanprojForecast.Stim_IncreOil,
                    md_pub.TbOilwellstimPlanprojForecast.Update_Date,

                    md_pub.TbYearDvpunitPlanprojOilstimCost.Stim_Cost,
                    md_pub.TbYearDvpunitPlanprojOilstimCost.DvpUnit_Name,

                    md_pub.TbYearDvpunitPlanprojOilprice.Oil_CommodityRatio,
                    md_pub.TbYearDvpunitPlanprojOilprice.Oil_Price,

                    md_pub.TbYearDvpunitPlanprojDiscount.Money_Discount,
                    md_pub.TbYearDvpunitPlanprojDiscount.Remark
                ).filter(
                    md_pub.TbOilwellstimPlanprojForecast.DvpUnit_OilStim_PlanProj_Name == data[
                        'DvpUnit_OilStim_PlanProj_Name'],
                    md_pub.TbOilwellstimPlanprojForecast.Well_Name == data['Well_Name'],
                    md_pub.TbOilwellstimPlanprojForecast.Year == data['Year'],
                    md_pub.TbOilwellstimPlanprojForecast.StimType_Name == data['StimType_Name'],

                    md_pub.TbYearDvpunitPlanprojOilstimCost.DvpUnit_OilStim_PlanProj_Name == data[
                        'DvpUnit_OilStim_PlanProj_Name'],
                    md_pub.TbYearDvpunitPlanprojOilstimCost.DvpUnit_Name == data['DvpUnit_Name'],
                    md_pub.TbYearDvpunitPlanprojOilstimCost.Year == data['Year'],
                    md_pub.TbYearDvpunitPlanprojOilstimCost.StimType_Name == data['StimType_Name'],

                    md_pub.TbYearDvpunitPlanprojOilprice.DvpUnit_OilStimulate_PlanProj_Name == data[
                        'DvpUnit_OilStim_PlanProj_Name'],
                    md_pub.TbYearDvpunitPlanprojOilprice.DvpUnit_Name == data['DvpUnit_Name'],
                    md_pub.TbYearDvpunitPlanprojOilprice.Year == data['Year'],

                    md_pub.TbYearDvpunitPlanprojDiscount.DvpUnit_OilStimulate_PlanProj_Name == data[
                        'DvpUnit_OilStim_PlanProj_Name'],
                    md_pub.TbYearDvpunitPlanprojDiscount.DvpUnit_Name == data['DvpUnit_Name'],
                    md_pub.TbYearDvpunitPlanprojDiscount.Year == data['Year']
                ).first()
                if db_data:
                    result.append(db_data)
        result = [dict(zip(data.keys(), data)) for data in result]

        dvpunit_name_db = session_pub.query(md_pub.TbWellbasic.Belong_DvpUnit_Name).filter(
            md_pub.TbWellbasic.Well_Name.in_(well_name)
        ).all()
        all_dvpunit_name = set()
        for data in dvpunit_name_db:
            all_dvpunit_name.add(data[0])
        all_dvpunit_name = list(all_dvpunit_name)

        dvp_oilprocost = session_pub.query(md_pub.TbYearDvpunitPlanprojOilprodcost).filter(
            md_pub.TbYearDvpunitPlanprojOilprodcost.DvpUnit_OilStim_PlanProj_Name.in_(dvpUnit_oilstim_planproj_name),
            md_pub.TbYearDvpunitPlanprojOilprodcost.DvpUnit_Name.in_(all_dvpunit_name),
            md_pub.TbYearDvpunitPlanprojOilprodcost.Year >= year[0],
            md_pub.TbYearDvpunitPlanprojOilprodcost.Year <= year[1],
        ).all()
        dvp_oilprocost_result = list()
        for data in dvp_oilprocost:
            dvp_oilprocost_result.append(data.__dict__)

        dvp_oilsalecost_ratio = session_pub.query(md_pub.TbYearDvpunitPlanprojOilsalecostRatio).filter(
            md_pub.TbYearDvpunitPlanprojOilsalecostRatio.DvpUnit_OilStim_PlanProj_Name.in_(
                dvpUnit_oilstim_planproj_name),
            md_pub.TbYearDvpunitPlanprojOilsalecostRatio.DvpUnit_Name.in_(all_dvpunit_name),
            md_pub.TbYearDvpunitPlanprojOilsalecostRatio.Year >= year[0],
            md_pub.TbYearDvpunitPlanprojOilsalecostRatio.Year <= year[1],
        ).all()
        dvp_oilsalecost_ratio_result = list()
        for data in dvp_oilsalecost_ratio:
            dvp_oilsalecost_ratio_result.append(data.__dict__)

        return result, dvp_oilprocost_result, dvp_oilsalecost_ratio_result
    except:
        print(traceback.print_exc())
        return False, False, False


def well_cal_save(datas, dvp_planproj_polprodcost, dvp_planproj_oilsalecost_ratio, delete):
    try:
        save_success = list()
        for data in delete:
            try:
                db_data = session_pub.query(md_pub.TbOilwellstimPlanprojForecast).filter(
                    md_pub.TbOilwellstimPlanprojForecast.DvpUnit_OilStim_PlanProj_Name == data[
                        'DvpUnit_OilStim_PlanProj_Name'],
                    md_pub.TbOilwellstimPlanprojForecast.Well_Name == data['Well_Name'],
                    md_pub.TbOilwellstimPlanprojForecast.Year == data['Year'],
                    md_pub.TbOilwellstimPlanprojForecast.StimType_Name == data['StimType_Name']
                )
                if db_data.first():
                    db_data.delete()
                    session_pub.commit()
                    save_success.append({
                        'DvpUnit_OilStim_PlanProj_Name': data['DvpUnit_OilStim_PlanProj_Name'],
                        'Well_Name': data['Well_Name'],
                        'Year': data['Year'],
                        'StimType_Name': data['StimType_Name'],
                        'result': True,
                        'message': '删除成功'
                    })
                else:
                    save_success.append({
                        'DvpUnit_OilStim_PlanProj_Name': data['DvpUnit_OilStim_PlanProj_Name'],
                        'Well_Name': data['Well_Name'],
                        'Year': data['Year'],
                        'StimType_Name': data['StimType_Name'],
                        'result': False,
                        'message': '无此数据'
                    })
            except Exception as e:
                print(e, e.__traceback__.tb_lineno)
                session_pub.rollback()
                save_success.append({
                    'DvpUnit_OilStim_PlanProj_Name': data['DvpUnit_OilStim_PlanProj_Name'],
                    'Well_Name': data['Well_Name'],
                    'Year': data['Year'],
                    'StimType_Name': data['StimType_Name'],
                    'result': False,
                    'message': '异常'
                })
        for data in datas:
            db_data = session_pub.query(
                md_pub.TbOilwellstimPlanprojForecast
            ).filter(
                md_pub.TbOilwellstimPlanprojForecast.DvpUnit_OilStim_PlanProj_Name == data[
                    'DvpUnit_OilStim_PlanProj_Name'],
                md_pub.TbOilwellstimPlanprojForecast.Well_Name == data['Well_Name'],
                md_pub.TbOilwellstimPlanprojForecast.Year == data['Year'],
                md_pub.TbOilwellstimPlanprojForecast.StimType_Name == data['StimType_Name']
            )
            data.update({
                'Update_Date': datetime.datetime.now().date()
            })
            dvpunit_name = session_pub.query(
                md_pub.TbWellbasic.Belong_DvpUnit_Name
            ).filter(
                md_pub.TbWellbasic.Well_Name == data['Well_Name']
            ).first()
            if dvpunit_name:
                dvpunit_name = dvpunit_name[0]
                data['DvpUnit_Name'] = dvpunit_name
            else:
                save_success.append({
                    'DvpUnit_OilStim_PlanProj_Name': data['DvpUnit_OilStim_PlanProj_Name'],
                    'Well_Name': data['Well_Name'],
                    'save': 'false',
                    'error': '该井没有所属单元'
                })
                return save_success
            if db_data.first():
                db_data.update({
                    'Forecast_Method': data['Forecast_Method'],
                    'Stim_ValidPeriod': data['Stim_ValidPeriod'],
                    'Stim_IncreOil': data['Stim_IncreOil'],
                    'Update_Date': data['Update_Date'],
                    'Remark': data['Remark']
                })
            else:
                session_pub.add(md_pub.TbOilwellstimPlanprojForecast(
                    DvpUnit_OilStim_PlanProj_Name=data['DvpUnit_OilStim_PlanProj_Name'],
                    Well_Name=data['Well_Name'],
                    Year=data['Year'],
                    StimType_Name=data['StimType_Name'],
                    Forecast_Method=data['Forecast_Method'],
                    Stim_ValidPeriod=data['Stim_ValidPeriod'],
                    Stim_IncreOil=data['Stim_IncreOil'],
                    Update_Date=data['Update_Date'],
                    Remark=data['Remark']
                ))
            session_pub.commit()
            if data.get('DvpUnit_Name'):
                db_data = session_pub.query(md_pub.TbYearDvpunitPlanprojOilstimCost).filter(
                    md_pub.TbYearDvpunitPlanprojOilstimCost.DvpUnit_OilStim_PlanProj_Name == data[
                        'DvpUnit_OilStim_PlanProj_Name'],
                    md_pub.TbYearDvpunitPlanprojOilstimCost.DvpUnit_Name == data['DvpUnit_Name'],
                    md_pub.TbYearDvpunitPlanprojOilstimCost.Year == data['Year'],
                    md_pub.TbYearDvpunitPlanprojOilstimCost.StimType_Name == data['StimType_Name']
                )
                if db_data.first():
                    db_data.update({
                        'Stim_Cost': data['Stim_Cost']
                    })
                else:
                    session_pub.add(md_pub.TbYearDvpunitPlanprojOilstimCost(
                        DvpUnit_OilStim_PlanProj_Name=data['DvpUnit_OilStim_PlanProj_Name'],
                        DvpUnit_Name=data['DvpUnit_Name'],
                        Year=data['Year'],
                        StimType_Name=data['StimType_Name'],
                        Stim_Cost=data['Stim_Cost'],
                        Remark=data['Remark']
                    ))
                session_pub.commit()

                db_data = session_pub.query(md_pub.TbYearDvpunitPlanprojOilprice).filter(
                    md_pub.TbYearDvpunitPlanprojOilprice.DvpUnit_OilStimulate_PlanProj_Name == data[
                        'DvpUnit_OilStim_PlanProj_Name'],
                    md_pub.TbYearDvpunitPlanprojOilprice.DvpUnit_Name == data['DvpUnit_Name'],
                    md_pub.TbYearDvpunitPlanprojOilprice.Year == data['Year']
                )
                if db_data.first():
                    db_data.update({
                        'Oil_CommodityRatio': data['Oil_CommodityRatio'],
                        'Oil_Price': data['Oil_Price'],
                        'Remark': data['Remark']
                    })
                else:
                    session_pub.add(md_pub.TbYearDvpunitPlanprojOilprice(
                        DvpUnit_OilStimulate_PlanProj_Name=data['DvpUnit_OilStim_PlanProj_Name'],
                        DvpUnit_Name=data['DvpUnit_Name'],
                        Year=data['Year'],
                        Oil_CommodityRatio=data['Oil_CommodityRatio'],
                        Oil_Price=data['Oil_Price'],
                        Remark=data['Remark']
                    ))
                session_pub.commit()

                db_data = session_pub.query(md_pub.TbYearDvpunitPlanprojDiscount).filter(
                    md_pub.TbYearDvpunitPlanprojDiscount.DvpUnit_OilStimulate_PlanProj_Name == data[
                        'DvpUnit_OilStim_PlanProj_Name'],
                    md_pub.TbYearDvpunitPlanprojDiscount.DvpUnit_Name == data['DvpUnit_Name'],
                    md_pub.TbYearDvpunitPlanprojDiscount.Year == data['Year']
                )
                if db_data.first():
                    db_data.update({
                        'Money_Discount': data['Money_Discount'],
                        'Remark': data['Remark']
                    })
                else:
                    session_pub.add(md_pub.TbYearDvpunitPlanprojDiscount(
                        DvpUnit_OilStimulate_PlanProj_Name=data['DvpUnit_OilStim_PlanProj_Name'],
                        DvpUnit_Name=data['DvpUnit_Name'],
                        Year=data['Year'],
                        Money_Discount=data['Money_Discount'],
                        Remark=data['Remark']
                    ))
                session_pub.commit()
            else:
                print(data)
            save_success.append({
                'DvpUnit_OilStim_PlanProj_Name': data['DvpUnit_OilStim_PlanProj_Name'],
                'Well_Name': data['Well_Name'],
                'DvpUnit_Name': data['DvpUnit_Name'],
                'Year': data['Year'],
                'StimType_Name': data['StimType_Name'],
                'result': True
            })
        for data in dvp_planproj_polprodcost:
            db_data = session_pub.query(md_pub.TbYearDvpunitPlanprojOilprodcost).filter(
                md_pub.TbYearDvpunitPlanprojOilprodcost.DvpUnit_OilStim_PlanProj_Name == data[
                    'DvpUnit_OilStim_PlanProj_Name'],
                md_pub.TbYearDvpunitPlanprojOilprodcost.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitPlanprojOilprodcost.Year == data['Year']
            )
            if db_data.first():
                db_data.update({
                    'Oil_OprCost': data['Oil_OprCost'],
                    'FuelCost': data['FuelCost'],
                    'Power_Cost': data['Power_Cost'],
                    'FluidDispose_Cost': data['FluidDispose_Cost'],
                    'OilDisplaceAgent_Cost': data['OilDisplaceAgent_Cost'],
                    'NGLRecovery_Cost': data['NGLRecovery_Cost'],
                    'Transp_Cost': data['Transp_Cost'],
                    'WellTest_Cost': data['WellTest_Cost'],
                    'Other_Cost': data['Other_Cost'],
                    'Depreciation_Cost': data['Depreciation_Cost'],
                    'Remark': data['Remark']
                })
            else:
                session_pub.add(md_pub.TbYearDvpunitPlanprojOilprodcost(
                    DvpUnit_OilStim_PlanProj_Name=data['DvpUnit_OilStim_PlanProj_Name'],
                    DvpUnit_Name=data['DvpUnit_Name'],
                    Year=data['Year'],
                    Oil_OprCost=data['Oil_OprCost'],
                    FuelCost=data['FuelCost'],
                    Power_Cost=data['Power_Cost'],
                    FluidDispose_Cost=data['FluidDispose_Cost'],
                    OilDisplaceAgent_Cost=data['OilDisplaceAgent_Cost'],
                    NGLRecovery_Cost=data['NGLRecovery_Cost'],
                    Transp_Cost=data['Transp_Cost'],
                    WellTest_Cost=data['WellTest_Cost'],
                    Other_Cost=data['Other_Cost'],
                    Depreciation_Cost=data['Depreciation_Cost'],
                    Remark=data['Remark']
                ))
            session_pub.commit()
            save_success.append({
                'DvpUnit_OilStim_PlanProj_Name': data['DvpUnit_OilStim_PlanProj_Name'],
                'DvpUnit_Name': data['DvpUnit_Name'],
                'Year': data['Year'],
                'result': True
            })
        for data in dvp_planproj_oilsalecost_ratio:
            db_data = session_pub.query(md_pub.TbYearDvpunitPlanprojOilsalecostRatio).filter(
                md_pub.TbYearDvpunitPlanprojOilsalecostRatio.DvpUnit_OilStim_PlanProj_Name == data[
                    'DvpUnit_OilStim_PlanProj_Name'],
                md_pub.TbYearDvpunitPlanprojOilsalecostRatio.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitPlanprojOilsalecostRatio.Year == data['Year']
            )
            if db_data.first():
                db_data.update({
                    'OilSale_TotalTaxRatio': data['OilSale_TotalTaxRatio'],
                    'AddedValue_TaxRatio': data['AddedValue_TaxRatio'],
                    'Resource_TaxRatio': data['Resource_TaxRatio'],
                    'EduAdd_TaxRatio': data['EduAdd_TaxRatio'],
                    'CityDvpAdd_TaxRate': data['CityDvpAdd_TaxRate'],
                    'Income_TaxRate': data['Income_TaxRate'],
                    'ManageCost_Ratio': data['ManageCost_Ratio'],
                    'Remark': data['Remark']
                })
            else:
                session_pub.add(md_pub.TbYearDvpunitPlanprojOilsalecostRatio(
                    DvpUnit_OilStim_PlanProj_Name=data['DvpUnit_OilStim_PlanProj_Name'],
                    DvpUnit_Name=data['DvpUnit_Name'],
                    Year=data['Year'],
                    OilSale_TotalTaxRatio=data['OilSale_TotalTaxRatio'],
                    AddedValue_TaxRatio=data['AddedValue_TaxRatio'],
                    Resource_TaxRatio=data['Resource_TaxRatio'],
                    EduAdd_TaxRatio=data['EduAdd_TaxRatio'],
                    CityDvpAdd_TaxRate=data['CityDvpAdd_TaxRate'],
                    Income_TaxRate=data['Income_TaxRate'],
                    ManageCost_Ratio=data['ManageCost_Ratio'],
                    Remark=data['Remark']
                ))
            session_pub.commit()
            save_success.append({
                'DvpUnit_OilStim_PlanProj_Name': data['DvpUnit_OilStim_PlanProj_Name'],
                'DvpUnit_Name': data['DvpUnit_Name'],
                'Year': data['Year'],
                'result': True
            })

        return {
            "save_success": save_success
        }
    except Exception as e:
        print(repr(e), e.__traceback__.tb_lineno)
        session_pub.rollback()
        return False


def get_all_proj_name_db():
    try:
        result = set()
        db_datas = session_pub.query(md_pub.TbYearDvpunitOilstimPlanprojMessage.DvpUnit_OilStim_PlanProj_Name).all()
        if db_datas:
            for data in db_datas:
                result.add(data[0])
        return sorted(list(result))
    except:
        print(traceback.print_exc())
        return False


def well_output_save_data(datas):
    try:
        save_success = list()
        for data in datas:
            db_data = session_pub.query(md_pub.TbOilwellstimPlanprojEvaluateResult).filter(
                md_pub.TbOilwellstimPlanprojEvaluateResult.DvpUnit_OilStim_PlanProj_Name == data[
                    'DvpUnit_OilStim_PlanProj_Name'],
                md_pub.TbOilwellstimPlanprojEvaluateResult.Well_Name == data['Well_Name'],
                md_pub.TbOilwellstimPlanprojEvaluateResult.Year == data['Year'],
                md_pub.TbOilwellstimPlanprojEvaluateResult.StimType_Name == data['StimType_Name']
            )
            if db_data.first():
                db_data.update({
                    'Stim_ValidPeriod': data['Stim_ValidPeriod'],
                    'Stim_IncreOil': data['Stim_IncreOil'],
                    'StimOil_CommodityRatio': data['StimOil_CommodityRatio'],
                    'Oil_Price': data['Oil_Price'],
                    'OilSale_TotalTaxRatio': data['OilSale_TotalTaxRatio'],
                    'Money_Discount': data['Money_Discount'],
                    'Oil_OprCost': data['Oil_OprCost'],
                    'StimCost': data['StimCost'],
                    'StimOil_WellOprCost': data['StimOil_WellOprCost'],
                    'StimOil_Total_Tax': data['StimOil_Total_Tax'],
                    'StimOil_Gross_SalesIncome': data['StimOil_Gross_SalesIncome'],
                    'StimOil_Static_Gains': data['StimOil_Static_Gains'],
                    'StimOil_NPV': data['StimOil_NPV'],
                    'StimOil_ROI': data['StimOil_ROI'],
                    'StimOil_NPVR': data['StimOil_NPVR'],
                    'StimOil_FIRR': data['StimOil_FIRR'],
                    'Stim_IncreOil_Critical': data['Stim_IncreOil_Critical'],
                    'StimOil_Price_Critical': data['StimOil_Price_Critical'],
                    'Remark': data['Remark']
                })
            else:
                session_pub.add(md_pub.TbOilwellstimPlanprojEvaluateResult(
                    DvpUnit_OilStim_PlanProj_Name=data['DvpUnit_OilStim_PlanProj_Name'],
                    Well_Name=data['Well_Name'],
                    Year=data['Year'],
                    StimType_Name=data['StimType_Name'],
                    Stim_ValidPeriod=data['Stim_ValidPeriod'],
                    Stim_IncreOil=data['Stim_IncreOil'],
                    StimOil_CommodityRatio=data['StimOil_CommodityRatio'],
                    Oil_Price=data['Oil_Price'],
                    OilSale_TotalTaxRatio=data['OilSale_TotalTaxRatio'],
                    Money_Discount=data['Money_Discount'],
                    Oil_OprCost=data['Oil_OprCost'],
                    StimCost=data['StimCost'],
                    StimOil_WellOprCost=data['StimOil_WellOprCost'],
                    StimOil_Total_Tax=data['StimOil_Total_Tax'],
                    StimOil_Gross_SalesIncome=data['StimOil_Gross_SalesIncome'],
                    StimOil_Static_Gains=data['StimOil_Static_Gains'],
                    StimOil_NPV=data['StimOil_NPV'],
                    StimOil_ROI=data['StimOil_ROI'],
                    StimOil_NPVR=data['StimOil_NPVR'],
                    StimOil_FIRR=data['StimOil_FIRR'],
                    Stim_IncreOil_Critical=data['Stim_IncreOil_Critical'],
                    StimOil_Price_Critical=data['StimOil_Price_Critical'],
                    Remark=data['Remark']
                ))
                session_pub.commit()
            save_success.append({
                'DvpUnit_OilStim_PlanProj_Name': data['DvpUnit_OilStim_PlanProj_Name'],
                'Well_Name': data['Well_Name'],
                'Year': data['Year'],
                'StimType_Name': data['StimType_Name'],
                'result': True
            })
        return save_success
    except:
        print(traceback.print_exc())
        session_pub.rollback()
        return False


def get_all_proj_name_by_tydoper():
    try:
        result = set()
        db_data = session_pub.query(
            md_pub.TbYearDvpunitOilstimPlanprojEvaluateResult.DvpUnit_OilStim_PlanProj_Name).all()
        if db_data:
            for data in db_data:
                result.add(data[0])
        return list(result)
    except:
        print(traceback.print_exc())
        return False


def stimtype_tree(stimtype_list, StimType_Name):
    stimtype_list = sorted(stimtype_list, key=lambda x: x.get("StimType_Rank"))
    num = 1
    temp_list = list()
    for stimtype in stimtype_list:
        temp_dict = dict()
        if stimtype['Belong_StimType_Name'] == StimType_Name and stimtype['StimType_Name'] != StimType_Name:
            temp_dict.update({
                'id': f'{stimtype["StimType_ID"]}{num}',
                'title': stimtype['StimType_Name'],
            })
            num += 1
            child = stimtype_tree(stimtype_list, stimtype['StimType_Name'])
            if child:
                temp_dict.update({
                    'children': child
                })
            temp_list.append(temp_dict)
    return temp_list


def dvpunit_name_tree(dvp_list, DvpUnit_Name):
    dvp_list = sorted(dvp_list, key=lambda x: x.get("DvpUnit_Rank"))
    num = 1
    temp_list = list()
    for dvp in dvp_list:
        temp_dict = dict()
        if dvp['Belong_DvpUnit_Name'] == DvpUnit_Name and dvp['DvpUnit_Name'] != DvpUnit_Name:
            temp_dict.update({
                'id': f'{dvp["DvpUnit_ID"]}{num}',
                'title': dvp['DvpUnit_Name'],
            })
            num += 1
            child = dvpunit_name_tree(dvp_list, dvp['DvpUnit_Name'])
            if child:
                temp_dict.update({
                    'children': child
                })
            temp_list.append(temp_dict)
    return temp_list


def get_dvpname_inf(DvpUnit_OilStim_PlanProj_Name):
    try:
        dvpunit_name_set = set()
        stimtype_name_set = set()
        year_set = set()
        db_data = session_pub.query(
            md_pub.TbYearDvpunitOilstimPlanprojEvaluateResult.DvpUnit_Name
        ).filter(
            md_pub.TbYearDvpunitOilstimPlanprojEvaluateResult.DvpUnit_OilStim_PlanProj_Name == DvpUnit_OilStim_PlanProj_Name
        ).all()
        if db_data:
            for data in db_data:
                dvpunit_name_set.add(data[0])

        db_data = session_pub.query(
            md_pub.TbYearDvpunitOilstimPlanprojEvaluateResult.StimType_Name
        ).filter(
            md_pub.TbYearDvpunitOilstimPlanprojEvaluateResult.DvpUnit_OilStim_PlanProj_Name == DvpUnit_OilStim_PlanProj_Name
        ).all()
        if db_data:
            for data in db_data:
                stimtype_name_set.add(data[0])

        db_data = session_pub.query(
            md_pub.TbYearDvpunitOilstimPlanprojEvaluateResult.Year
        ).filter(
            md_pub.TbYearDvpunitOilstimPlanprojEvaluateResult.DvpUnit_OilStim_PlanProj_Name == DvpUnit_OilStim_PlanProj_Name
        ).all()
        if db_data:
            for data in db_data:
                year_set.add(data[0])
        dvpunit_name_list = list(dvpunit_name_set)
        stimtype_name_list = list(stimtype_name_set)
        year_list = sorted(list(year_set))
        all_dvp_in_db = session_pub.query(
            md_pub.TbDvpunitbaisc.DvpUnit_ID,
            md_pub.TbDvpunitbaisc.DvpUnit_Name,
            md_pub.TbDvpunitbaisc.Belong_DvpUnit_Name,
            md_pub.TbDvpunitbaisc.DvpUnit_Rank
        ).filter(
            md_pub.TbDvpunitbaisc.DvpUnit_Name.in_(dvpunit_name_list)
        ).all()
        all_dvp_in_db = [dict(zip(type.keys(), type)) for type in all_dvp_in_db]
        dvp_tree = list()
        dvp_list = sorted(all_dvp_in_db, key=lambda x: x["DvpUnit_Rank"])
        while len(dvp_list):
            item = dvp_list[0]
            temp_dict = dict()
            temp_dict.update({
                'id': item['DvpUnit_ID'],
                'title': item['DvpUnit_Name'],
            })
            child = dvpunit_name_tree(dvp_list, item['DvpUnit_Name'])
            if child:
                temp_dict.update({
                    'children': child
                })
            dvp_tree.append(temp_dict)
            del_name = set()
            new_list = list()
            del_name.add(item['DvpUnit_Name'])
            for x in dvp_list:
                if x['Belong_DvpUnit_Name'] in del_name or x['DvpUnit_Name'] in del_name:
                    del_name.add(x['DvpUnit_Name'])
                else:
                    new_list.append(x)
            dvp_list = new_list
        all_stimtype_in_db = session_pub.query(
            md_pub.TbOilwellstimType.StimType_Name,
            md_pub.TbOilwellstimType.Belong_StimType_Name,
            md_pub.TbOilwellstimType.StimType_Rank,
            md_pub.TbOilwellstimType.StimType_ID
        ).filter(
            md_pub.TbOilwellstimType.StimType_Name.in_(stimtype_name_list)
        ).all()
        all_stimtype_in_db = [dict(zip(type.keys(), type)) for type in all_stimtype_in_db]
        stim_type_tree = list()
        stimtype_list = sorted(all_stimtype_in_db, key=lambda x: x["StimType_Rank"])
        while len(stimtype_list):
            item = stimtype_list[0]
            temp_dict = dict()
            temp_dict.update({
                'id': item['StimType_ID'],
                'title': item['StimType_Name'],
            })
            child = stimtype_tree(stimtype_list, item['StimType_Name'])
            if child:
                temp_dict.update({
                    'children': child
                })
            stim_type_tree.append(temp_dict)
            del_name = set()
            new_list = list()
            del_name.add(item['StimType_Name'])
            for x in stimtype_list:
                if x['Belong_StimType_Name'] in del_name or x['StimType_Name'] in del_name:
                    del_name.add(x['StimType_Name'])
                else:
                    new_list.append(x)
            stimtype_list = new_list

        year = list()
        if year_list:
            year.append(year_list[0])
            year.append(year_list[len(year_list) - 1])
        return dvp_tree, stim_type_tree, year
    except:
        print(traceback.print_exc())
        return False


def unit_scheme_curve_readdb_fun(DvpUnit_Name, StimType_Name, Year):
    try:
        db_datas = session_pub.query(
            md_pub.TbYearDvpunitOilstimPlanprojEvaluateResult
        ).filter(
            md_pub.TbYearDvpunitOilstimPlanprojEvaluateResult.DvpUnit_Name == DvpUnit_Name,
            md_pub.TbYearDvpunitOilstimPlanprojEvaluateResult.StimType_Name == StimType_Name,
            md_pub.TbYearDvpunitOilstimPlanprojEvaluateResult.Year >= Year[0],
            md_pub.TbYearDvpunitOilstimPlanprojEvaluateResult.Year <= Year[1],
        ).all()
        result = list()
        if db_datas:
            result = [data.__dict__ for data in db_datas]
        return result
    except:
        print(traceback.print_exc())
        return False


def get_well_and_dvpname_db():
    try:
        dvp_tree = list()
        all_well = session_pub.query(md_pub.TbOilwellstimResult.Well_Name).all()
        if all_well:
            all_well_set = set()
            for data in all_well:
                all_well_set.add(data[0])
            all_well = list(all_well_set)
            all_well_belong_dvp = session_pub.query(
                md_pub.TbWellbasic.Belong_DvpUnit_Name
            ).filter(
                md_pub.TbWellbasic.Well_Name.in_(all_well)
            ).all()
            if all_well_belong_dvp:
                all_well_belong_dvp_set = set()
                for data in all_well_belong_dvp:
                    all_well_belong_dvp_set.add(data[0])
                all_well_belong_dvp = list(all_well_belong_dvp_set)
            all_dvp = session_pub.query(
                md_pub.TbDvpunitbaisc
            ).filter(
                md_pub.TbDvpunitbaisc.DvpUnit_Name.in_(all_well_belong_dvp)
            ).all()
            all_dvp = [data.__dict__ for data in all_dvp]
            dvp_list = sorted(all_dvp, key=lambda x: x["DvpUnit_Rank"])
            while len(dvp_list):
                print(dvp_list)
                item = dvp_list[0]
                temp_dict = dict()
                temp_dict.update({
                    'id': item['DvpUnit_ID'],
                    'title': item['DvpUnit_Name'],
                })
                child = dvpunit_name_tree(dvp_list, item['DvpUnit_Name'])
                if child:
                    temp_dict.update({
                        'children': child
                    })
                dvp_tree.append(temp_dict)
                del_name = set()
                new_list = list()
                del_name.add(item['DvpUnit_Name'])
                for x in dvp_list:
                    if x['Belong_DvpUnit_Name'] in del_name or x['DvpUnit_Name'] in del_name:
                        del_name.add(x['DvpUnit_Name'])
                    else:
                        new_list.append(x)
                dvp_list = new_list
        return dvp_tree
    except Exception as e:
        print(e, e.__traceback__.tb_lineno)
        return False


def get_well_by_dvpname_db(all_dvpunit_name):
    try:
        result_well = list()
        result_stimtype = list()
        result_date = list()
        db_datas = session_pub.query(
            md_pub.TbOilwellstimResult.Well_Name,
            md_pub.TbOilwellstimResult.StimType_Name
        ).all()
        db_datas = [dict(zip(data.keys(), data)) for data in db_datas]
        well_by_dvpname = list()
        for data in db_datas:
            db_data = session_pub.query(
                md_pub.TbOilwellstimResult.Well_Name,
                md_pub.TbOilwellstimResult.Stim_Date,
                md_pub.TbOilwellstimResult.StimType_Name,
                md_pub.TbWellbasic.Belong_DvpUnit_Name
            ).filter(
                md_pub.TbOilwellstimResult.Well_Name == data['Well_Name'],
                md_pub.TbOilwellstimResult.StimType_Name == data['StimType_Name'],
                md_pub.TbWellbasic.Well_Name == data['Well_Name'],
                md_pub.TbWellbasic.Belong_DvpUnit_Name.in_(all_dvpunit_name)
            ).first()
            if db_data:
                db_data = dict(zip(db_data.keys(), db_data))
                well_by_dvpname.append(db_data)
        count = 0
        for data in all_dvpunit_name:
            temp_dict_well = dict()
            temp_dict_well.update({
                'id': count,
                'title': data,
                'children': get_children(well_by_dvpname, data, 'Belong_DvpUnit_Name')
            })
            if not temp_dict_well['children']:
                del temp_dict_well['children']
            result_well.append(temp_dict_well)

            temp_dict_stimtype = dict()
            temp_dict_stimtype.update({
                'id': count,
                'title': data,
                'children': get_children(well_by_dvpname, data, 'StimType_Name')
            })
            if not temp_dict_stimtype['children']:
                del temp_dict_stimtype['children']
            result_stimtype.append(temp_dict_stimtype)

            temp_dict_date = dict()
            temp_dict_date.update({
                'id': count,
                'title': data,
                'children': sorted(get_children(well_by_dvpname, data, 'Stim_Date'))
            })
            date_list = list()
            date_list.append(temp_dict_date['children'][0])
            date_list.append(temp_dict_date['children'][len(temp_dict_date['children']) - 1])
            temp_dict_date.update({
                'children': date_list
            })
            if not temp_dict_date['children']:
                del temp_dict_date['children']
            result_date.append(temp_dict_date)
            count += 1

        return result_well, result_stimtype, result_date
    except:
        print(traceback.print_exc())
        return False


def get_children(all_list, parent_name, type):
    result = set()
    for data in all_list:
        if data['Belong_DvpUnit_Name'] == parent_name:
            if type == 'Belong_DvpUnit_Name':
                result.add(data['Well_Name'])
            if type == 'StimType_Name':
                result.add(data['StimType_Name'])
            if type == 'Stim_Date':
                result.add(data['Stim_Date'])
    result = sorted(list(result))
    return result


def addwell_function(Well_Name, GeoCoordX, GeoCoordY, Belong_DvpUnit_Name, Remark):
    try:
        db_data = session_pub.query(md_pub.TbWellbasic).filter(
            md_pub.TbWellbasic.Well_Name == Well_Name
        ).first()
        result = dict()
        if db_data:
            session_pub.query(md_pub.TbWellbasic).filter(
                md_pub.TbWellbasic.Well_Name == Well_Name
            ).update({
                'GeoCoordX': GeoCoordX,
                'GeoCoordY': GeoCoordY,
                'Belong_DvpUnit_Name': Belong_DvpUnit_Name,
                'Remark': Remark
            })
            result.update({
                'Well_Name': Well_Name,
                'update': True
            })
        else:
            session_pub.add(md_pub.TbWellbasic(
                Well_Name=Well_Name,
                GeoCoordX=GeoCoordX,
                GeoCoordY=GeoCoordY,
                Belong_DvpUnit_Name=Belong_DvpUnit_Name,
                Remark=Remark
            ))
            result.update({
                'Well_Name': Well_Name,
                'add': True
            })
        session_pub.commit()
        return result
    except Exception as e:
        print(e, e.__traceback__.tb_lineno)
        session_pub.rollback()
        return False


def planproj_oilprodcost(cost_data):
    return_data = list()
    for data in cost_data:
        try:
            is_existed = session_pub.query(md_pub.TbYearDvpunitPlanprojOilprodcost).filter(
                md_pub.TbYearDvpunitPlanprojOilprodcost.DvpUnit_OilStim_PlanProj_Name == data[
                    'DvpUnit_OilStim_PlanProj_Name'],
                md_pub.TbYearDvpunitPlanprojOilprodcost.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitPlanprojOilprodcost.Year == data['Year']
            ).count()
            if is_existed:
                session_pub.query(md_pub.TbYearDvpunitPlanprojOilprodcost).filter(
                    md_pub.TbYearDvpunitPlanprojOilprodcost.DvpUnit_OilStim_PlanProj_Name == data[
                        "DvpUnit_OilStim_PlanProj_Name"],
                    md_pub.TbYearDvpunitPlanprojOilprodcost.DvpUnit_Name == data["DvpUnit_Name"],
                    md_pub.TbYearDvpunitPlanprojOilprodcost.Year == data["Year"]
                ).update({
                    md_pub.TbYearDvpunitPlanprojOilprodcost.Oil_OprCost: data["Oil_OprCost"],
                    md_pub.TbYearDvpunitPlanprojOilprodcost.FuelCost: data["FuelCost"],
                    md_pub.TbYearDvpunitPlanprojOilprodcost.Power_Cost: data["Power_Cost"],
                    md_pub.TbYearDvpunitPlanprojOilprodcost.FluidDispose_Cost: data["FluidDispose_Cost"],
                    md_pub.TbYearDvpunitPlanprojOilprodcost.OilDisplaceAgent_Cost: data["OilDisplaceAgent_Cost"],
                    md_pub.TbYearDvpunitPlanprojOilprodcost.NGLRecovery_Cost: data["NGLRecovery_Cost"],
                    md_pub.TbYearDvpunitPlanprojOilprodcost.Transp_Cost: data["Transp_Cost"],
                    md_pub.TbYearDvpunitPlanprojOilprodcost.WellTest_Cost: data["WellTest_Cost"],
                    md_pub.TbYearDvpunitPlanprojOilprodcost.Other_Cost: data["Other_Cost"],
                    md_pub.TbYearDvpunitPlanprojOilprodcost.Depreciation_Cost: data["Depreciation_Cost"],
                    md_pub.TbYearDvpunitPlanprojOilprodcost.Remark: data["Remark"]
                })
            else:
                add_data = md_pub.TbYearDvpunitPlanprojOilprodcost(
                    DvpUnit_OilStim_PlanProj_Name=data["DvpUnit_OilStim_PlanProj_Name"],
                    DvpUnit_Name=data["DvpUnit_Name"],
                    Year=data["Year"],
                    Oil_OprCost=data["Oil_OprCost"],
                    FuelCost=data["FuelCost"],
                    Power_Cost=data["Power_Cost"],
                    FluidDispose_Cost=data["FluidDispose_Cost"],
                    OilDisplaceAgent_Cost=data["OilDisplaceAgent_Cost"],
                    NGLRecovery_Cost=data["NGLRecovery_Cost"],
                    Transp_Cost=data["Transp_Cost"],
                    WellTest_Cost=data["WellTest_Cost"],
                    Other_Cost=data["Other_Cost"],
                    Depreciation_Cost=data["Depreciation_Cost"],
                    Remark=data["Remark"]
                )
                session_pub.add(add_data)
            session_pub.commit()
            return_data.append(
                {
                    'DvpUnit_OilStim_PlanProj_Name': data['DvpUnit_OilStim_PlanProj_Name'],
                    'DvpUnit_Name': data['DvpUnit_Name'],
                    'Year': data['Year'],
                    'result': True
                }
            )

        except:
            return_data.append(
                {
                    'DvpUnit_OilStim_PlanProj_Name': data['DvpUnit_OilStim_PlanProj_Name'],
                    'DvpUnit_Name': data['DvpUnit_Name'],
                    'Year': data['Year'],
                    'result': False
                }
            )
            session_pub.rollback()
            print(traceback.print_exc())
    return return_data


def planproj_oilsalecost_ratio(radio_data):
    return_data = list()
    for data in radio_data:
        try:
            is_existed = session_pub.query(md_pub.TbYearDvpunitPlanprojOilsalecostRatio).filter(
                md_pub.TbYearDvpunitPlanprojOilsalecostRatio.DvpUnit_OilStim_PlanProj_Name == data[
                    'DvpUnit_OilStim_PlanProj_Name'],
                md_pub.TbYearDvpunitPlanprojOilsalecostRatio.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitPlanprojOilsalecostRatio.Year == data['Year']
            ).count()
            if is_existed:
                session_pub.query(md_pub.TbYearDvpunitPlanprojOilsalecostRatio).filter(
                    md_pub.TbYearDvpunitPlanprojOilsalecostRatio.DvpUnit_Name == data["DvpUnit_Name"],
                    md_pub.TbYearDvpunitPlanprojOilsalecostRatio.Year == data["Year"]
                ).update({
                    md_pub.TbYearDvpunitPlanprojOilsalecostRatio.OilSale_TotalTaxRatio: data["OilSale_TotalTaxRatio"],
                    md_pub.TbYearDvpunitPlanprojOilsalecostRatio.AddedValue_TaxRatio: data["AddedValue_TaxRatio"],
                    md_pub.TbYearDvpunitPlanprojOilsalecostRatio.Resource_TaxRatio: data["Resource_TaxRatio"],
                    md_pub.TbYearDvpunitPlanprojOilsalecostRatio.EduAdd_TaxRatio: data["EduAdd_TaxRatio"],
                    md_pub.TbYearDvpunitPlanprojOilsalecostRatio.CityDvpAdd_TaxRate: data["CityDvpAdd_TaxRate"],
                    md_pub.TbYearDvpunitPlanprojOilsalecostRatio.Income_TaxRate: data["Income_TaxRate"],
                    md_pub.TbYearDvpunitPlanprojOilsalecostRatio.ManageCost_Ratio: data["ManageCost_Ratio"],
                    md_pub.TbYearDvpunitPlanprojOilsalecostRatio.Remark: data["Remark"]
                })
            else:
                add_data = md_pub.TbYearDvpunitPlanprojOilsalecostRatio(
                    DvpUnit_OilStim_PlanProj_Name=data["DvpUnit_OilStim_PlanProj_Name"],
                    DvpUnit_Name=data["DvpUnit_Name"],
                    Year=data["Year"],
                    OilSale_TotalTaxRatio=data["OilSale_TotalTaxRatio"],
                    AddedValue_TaxRatio=data["AddedValue_TaxRatio"],
                    Resource_TaxRatio=data["Resource_TaxRatio"],
                    EduAdd_TaxRatio=data["EduAdd_TaxRatio"],
                    CityDvpAdd_TaxRate=data["CityDvpAdd_TaxRate"],
                    Income_TaxRate=data["Income_TaxRate"],
                    ManageCost_Ratio=data["ManageCost_Ratio"],
                    Remark=data["Remark"]
                )
                session_pub.add(add_data)
            session_pub.commit()
            return_data.append({
                'DvpUnit_OilStim_PlanProj_Name': data['DvpUnit_OilStim_PlanProj_Name'],
                'DvpUnit_Name': data['DvpUnit_Name'],
                'Year': data['Year'],
                'result': True
            })

        except:
            return_data.append({
                'DvpUnit_OilStim_PlanProj_Name': data['DvpUnit_OilStim_PlanProj_Name'],
                'DvpUnit_Name': data['DvpUnit_Name'],
                'Year': data['Year'],
                'result': False
            })
            session_pub.rollback()
            print(traceback.print_exc())
    return return_data


def forecast_delete_result(delete):
    result = list()
    for data in delete:
        try:
            db_data = session_pub.query(md_pub.TbYearDvpunitOilstimPlanprojForecast).filter(
                md_pub.TbYearDvpunitOilstimPlanprojForecast.DvpUnit_OilStim_PlanProj_Name == data[
                    'DvpUnit_OilStim_PlanProj_Name'],
                md_pub.TbYearDvpunitOilstimPlanprojForecast.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitOilstimPlanprojForecast.Year == data['Year'],
                md_pub.TbYearDvpunitOilstimPlanprojForecast.StimType_Name == data['StimType_Name']
            )
            if db_data.first():
                db_data.delete()
                session_pub.commit()
                result.append({
                    'DvpUnit_OilStim_PlanProj_Name': data['DvpUnit_OilStim_PlanProj_Name'],
                    'DvpUnit_Name': data['DvpUnit_Name'],
                    'Year': data['Year'],
                    'StimType_Name': data['StimType_Name'],
                    'result': True,
                    'message': '删除成功',
                })
            else:
                result.append({
                    'DvpUnit_OilStim_PlanProj_Name': data['DvpUnit_OilStim_PlanProj_Name'],
                    'DvpUnit_Name': data['DvpUnit_Name'],
                    'Year': data['Year'],
                    'StimType_Name': data['StimType_Name'],
                    'result': False,
                    'message': '无此数据',
                })
        except Exception as e:
            print(e, e.__traceback__.tb_lineno)
            session_pub.rollback()
            result.append({
                'DvpUnit_OilStim_PlanProj_Name': data['DvpUnit_OilStim_PlanProj_Name'],
                'DvpUnit_Name': data['DvpUnit_Name'],
                'Year': data['Year'],
                'StimType_Name': data['StimType_Name'],
                'result': False,
                'message': '异常',
            })
    return result


def read_db_all_well():
    try:
        result = set()
        db_data = session_pub.query(md_pub.TbWellbasic.Well_Name).all()
        if db_data:
            for data in db_data:
                result.add(data[0])
        result = sorted(list(result))
        return result
    except Exception:
        print(traceback.print_exc())
        return False


def read_oilstim_result_db(datas):
    result = list()
    for data in datas:
        try:
            db_datas = session_pub.query(md_pub.TbYearDvpunitOilstimResult).filter(
                md_pub.TbYearDvpunitOilstimResult.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitOilstimResult.StimType_Name == data['StimType_Name'],
                md_pub.TbYearDvpunitOilstimResult.Year >= data['Start_Year'],
                md_pub.TbYearDvpunitOilstimResult.Year <= data['End_Year']
            ).all()
            if db_datas:
                result = [data.__dict__ for data in db_datas]
        except Exception as e:
            print(e, e.__traceback__.tb_lineno)
            return False
    return result


def save_oilstim_result_db(delete, add, modify):
    result = list()
    for data in delete:
        try:
            db_data = session_pub.query(md_pub.TbYearDvpunitOilstimResult).filter(
                md_pub.TbYearDvpunitOilstimResult.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitOilstimResult.StimType_Name == data['StimType_Name'],
                md_pub.TbYearDvpunitOilstimResult.Year == data['Year']
            )
            if db_data.first():
                db_data.delete()
                session_pub.commit()
                result.append({
                    'DvpUnit_Name': data['DvpUnit_Name'],
                    'StimType_Name': data['StimType_Name'],
                    'Year': data['Year'],
                    'result': True,
                    'message': '删除成功'
                })
            else:
                result.append({
                    'DvpUnit_Name': data['DvpUnit_Name'],
                    'StimType_Name': data['StimType_Name'],
                    'Year': data['Year'],
                    'result': False,
                    'message': '删除失败,数据不存在'
                })
        except Exception as e:
            print(e, e.__traceback__.tb_lineno)
            session_pub.rollback()
            result.append({
                'DvpUnit_Name': data['DvpUnit_Name'],
                'StimType_Name': data['StimType_Name'],
                'Year': data['Year'],
                'result': False,
                'message': '异常,删除失败'
            })
    datas = add + modify
    for data in datas:
        try:
            db_data = session_pub.query(md_pub.TbYearDvpunitOilstimResult).filter(
                md_pub.TbYearDvpunitOilstimResult.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitOilstimResult.StimType_Name == data['StimType_Name'],
                md_pub.TbYearDvpunitOilstimResult.Year == data['Year']
            )
            if db_data.first():
                db_data.update({
                    'Stim_WellNumber_Valid': data['Stim_WellNumber_Valid'],
                    'Stim_WellValidRatio': data['Stim_WellValidRatio'],
                    'Stim_ValidPeriod_Average': data['Stim_ValidPeriod_Average'],
                    'Stim_IncreOil_Average': data['Stim_IncreOil_Average']
                })
                session_pub.commit()
                result.append({
                    'DvpUnit_Name': data['DvpUnit_Name'],
                    'StimType_Name': data['StimType_Name'],
                    'Year': data['Year'],
                    'result': True,
                    'message': '修改成功'
                })
            else:
                session_pub.add(md_pub.TbYearDvpunitOilstimResult(
                    DvpUnit_Name=data['DvpUnit_Name'],
                    StimType_Name=data['StimType_Name'],
                    Year=data['Year'],
                    Stim_WellNumber_Valid=data['Stim_WellNumber_Valid'],
                    Stim_WellValidRatio=data['Stim_WellValidRatio'],
                    Stim_ValidPeriod_Average=data['Stim_ValidPeriod_Average'],
                    Stim_IncreOil_Average=data['Stim_IncreOil_Average']
                ))
                session_pub.commit()
                result.append({
                    'DvpUnit_Name': data['DvpUnit_Name'],
                    'StimType_Name': data['StimType_Name'],
                    'Year': data['Year'],
                    'result': True,
                    'message': '新增数据成功'
                })
        except Exception as e:
            print(e, e.__traceback__.tb_lineno)
            session_pub.rollback()
            result.append({
                'DvpUnit_Name': data['DvpUnit_Name'],
                'StimType_Name': data['StimType_Name'],
                'Year': data['Year'],
                'result': False,
                'message': '操作失败'
            })

    return result

def read_oilstim_planproj_forecast_db(DvpUnit_Name, StimType_Name, Start_Year, End_Year):
    result = list()
    try:
        db_datas = session_pub.query(md_pub.TbYearDvpunitOilstimPlanprojForecast).filter(
            md_pub.TbYearDvpunitOilstimPlanprojForecast.DvpUnit_Name.in_(DvpUnit_Name),
            md_pub.TbYearDvpunitOilstimPlanprojForecast.StimType_Name.in_(StimType_Name),
            md_pub.TbYearDvpunitOilstimPlanprojForecast.Year >= Start_Year,
            md_pub.TbYearDvpunitOilstimPlanprojForecast.Year <= End_Year
        ).all()
        if db_datas:
            result = [data.__dict__ for data in db_datas]
    except Exception as e:
        print(e, e.__traceback__.tb_lineno)
        return False
    return result

def save_oilstim_planproj_forecast_db(delete, add, modify):
    result = list()
    for data in delete:
        try:
            db_data = session_pub.query(md_pub.TbYearDvpunitOilstimPlanprojForecast).filter(
                md_pub.TbYearDvpunitOilstimPlanprojForecast.DvpUnit_OilStim_PlanProj_Name == data['DvpUnit_OilStim_PlanProj_Name'],
                md_pub.TbYearDvpunitOilstimPlanprojForecast.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitOilstimPlanprojForecast.StimType_Name == data['StimType_Name'],
                md_pub.TbYearDvpunitOilstimPlanprojForecast.Year == data['Year']
            )
            if db_data.first():
                db_data.delete()
                session_pub.commit()
                result.append({
                    'DvpUnit_OilStim_PlanProj_Name': data['DvpUnit_OilStim_PlanProj_Name'],
                    'DvpUnit_Name': data['DvpUnit_Name'],
                    'StimType_Name': data['StimType_Name'],
                    'Year': data['Year'],
                    'result': True,
                    'message': '删除成功'
                })
            else:
                result.append({
                    'DvpUnit_OilStim_PlanProj_Name': data['DvpUnit_OilStim_PlanProj_Name'],
                    'DvpUnit_Name': data['DvpUnit_Name'],
                    'StimType_Name': data['StimType_Name'],
                    'Year': data['Year'],
                    'result': False,
                    'message': '删除失败,数据不存在'
                })
        except Exception as e:
            print(e, e.__traceback__.tb_lineno)
            session_pub.rollback()
            result.append({
                'DvpUnit_Name': data['DvpUnit_Name'],
                'StimType_Name': data['StimType_Name'],
                'Year': data['Year'],
                'result': False,
                'message': '异常,删除失败'
            })
    datas = add + modify
    for data in datas:
        data.update({
            'Update_Date': datetime.datetime.now()
        })
        try:
            db_data = session_pub.query(md_pub.TbYearDvpunitOilstimPlanprojForecast).filter(
                md_pub.TbYearDvpunitOilstimPlanprojForecast.DvpUnit_OilStim_PlanProj_Name == data[
                    'DvpUnit_OilStim_PlanProj_Name'],
                md_pub.TbYearDvpunitOilstimPlanprojForecast.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitOilstimPlanprojForecast.StimType_Name == data['StimType_Name'],
                md_pub.TbYearDvpunitOilstimPlanprojForecast.Year == data['Year']
            )
            if db_data.first():
                db_data.update({
                    'Stim_WellValidRatio': data['Stim_WellValidRatio'],
                    'Stim_WellNumber_Valid': data['Stim_WellNumber_Valid'],
                    'Stim_ValidPeriod_Average': data['Stim_ValidPeriod_Average'],
                    'Stim_IncreOil_Average': data['Stim_IncreOil_Average'],
                    'Update_Date': data['Update_Date'],
                    'Remark': data['Remark']
                })
                session_pub.commit()
                result.append({
                    'DvpUnit_OilStim_PlanProj_Name': data['DvpUnit_OilStim_PlanProj_Name'],
                    'DvpUnit_Name': data['DvpUnit_Name'],
                    'StimType_Name': data['StimType_Name'],
                    'Year': data['Year'],
                    'result': True,
                    'message': '修改成功'
                })
            else:
                session_pub.add(md_pub.TbYearDvpunitOilstimPlanprojForecast(
                    DvpUnit_OilStim_PlanProj_Name=data['DvpUnit_OilStim_PlanProj_Name'],
                    DvpUnit_Name=data['DvpUnit_Name'],
                    Year=data['Year'],
                    StimType_Name=data['StimType_Name'],
                    Stim_WellValidRatio=data['Stim_WellValidRatio'],
                    Stim_WellNumber_Valid=data['Stim_WellNumber_Valid'],
                    Stim_ValidPeriod_Average=data['Stim_ValidPeriod_Average'],
                    Stim_IncreOil_Average=data['Stim_IncreOil_Average'],
                    Update_Date=data['Update_Date'],
                    Remark=data['Remark']
                ))
                session_pub.commit()
                result.append({
                    'DvpUnit_OilStim_PlanProj_Name': data['DvpUnit_OilStim_PlanProj_Name'],
                    'DvpUnit_Name': data['DvpUnit_Name'],
                    'StimType_Name': data['StimType_Name'],
                    'Year': data['Year'],
                    'result': True,
                    'message': '新增成功'
                })
        except Exception as e:
            print(e, e.__traceback__.tb_lineno)
            session_pub.rollback()
            result.append({
                'DvpUnit_OilStim_PlanProj_Name': data['DvpUnit_OilStim_PlanProj_Name'],
                'DvpUnit_Name': data['DvpUnit_Name'],
                'StimType_Name': data['StimType_Name'],
                'Year': data['Year'],
                'result': False,
                'message': '操作失败'
            })
    return result